package com.lsyw.intergrate.mapper;

import com.lsyw.intergrate.entity.Massif;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.lsyw.intergrate.entity.MassifPlan;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * 地块 Mapper 接口
 * </p>
 *
 * @author 刘磊
 * @since 2023-12-13
 */
@Mapper
public interface MassifMapper extends BaseMapper<Massif> {

    @Select("select mp.* from massif_plan mp \n" +
            "left join massif m on m.id=mp.massif_id\n" +
            "where mp.del_flag=0 and m.del_flag=0  and mp.date<DATE_ADD(NOW(), INTERVAL 3 DAY)\n" +
            "and (mp.soil_percent*m.trxfzfl/100)>IFNULL((select sum(mr.soil_epair) from massif_record mr where mr.del_flag=0 and mp.massif_id=mr.massif_id),0.0)\n" +
            "and (mp.water_percent*m.dxsxfl/100)>IFNULL((select sum(mr.ground_water) from massif_record mr where mr.del_flag=0 and mp.massif_id=mr.massif_id),0.0)")
    List<MassifPlan> getAllAlarm();

    @Select("select mp.* from massif_plan mp \n" +
            "left join massif m on m.id=mp.massif_id\n" +
            "where mp.del_flag=0 and m.del_flag=0  and mp.date<DATE_ADD(NOW(), INTERVAL 3 DAY)\n" +
            "and (mp.soil_percent*m.trxfzfl/100)<=IFNULL((select sum(mr.soil_epair) from massif_record mr where mr.del_flag=0 and mp.massif_id=mr.massif_id),0.0)\n" +
            "and (mp.water_percent*m.dxsxfl/100)>IFNULL((select sum(mr.ground_water) from massif_record mr where mr.del_flag=0 and mp.massif_id=mr.massif_id),0.0)")
    List<MassifPlan> getWaterAlarm();

    @Select("select mp.* from massif_plan mp \n" +
            "left join massif m on m.id=mp.massif_id\n" +
            "where mp.del_flag=0 and m.del_flag=0  and mp.date<DATE_ADD(NOW(), INTERVAL 3 DAY)\n" +
            "and (mp.soil_percent*m.trxfzfl/100)>IFNULL((select sum(mr.soil_epair) from massif_record mr where mr.del_flag=0 and mp.massif_id=mr.massif_id),0.0)\n" +
            "and (mp.water_percent*m.dxsxfl/100)<=IFNULL((select sum(mr.ground_water) from massif_record mr where mr.del_flag=0 and mp.massif_id=mr.massif_id),0.0)")
    List<MassifPlan> getSoilAlarm();
}
